
' 创建数据表V5
Sub createTabAndFmt()
  Dim FileName As String
  Dim TableName, sheetName As String
  Dim ListRow As Integer
  Dim ListRow2 As Integer
  Dim ms, zdm, lx, isK As String
  Dim count As Integer
  
  FileName = Application.ActiveWorkbook.Path & "\table.sql"
  Open (FileName) For Output As #1
  
  count = 0
  For ListRow = 2 To Range("C65536").End(xlUp).Row
    If Trim(Worksheets("目录").Range("D" & ListRow).Value) = "是" Then
        TableName = Worksheets("目录").Range("C" & ListRow).Value
        sheetName = Worksheets("目录").Range("C" & ListRow).Value
    
        '跳到该sheet
        Sheets(sheetName).Select
        
        'start fmt
        Dim endRow As Integer
    
        '最大的行数
        endRow = Range("C65536").End(xlUp).Row
        
        '第一行高度
        Rows("1:1").RowHeight = 30
        
        '序号排上
        Range("A2:A3").Select
        Selection.AutoFill Destination:=Range("A2:A" & endRow), Type:=xlFillDefault
         
         '有数据的选择上,然后改变字体，和格式线
        Range("A1:E" & endRow).Select
        
        '字体和大小
        With Selection.Font
            .Name = "微软雅黑"
            .Size = 12
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .TintAndShade = 0
            .ThemeFont = xlThemeFontNone
        End With
        
        '加上格式线
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        
        Columns("A:A").ColumnWidth = 10
        Columns("B:B").ColumnWidth = 22
        Columns("C:C").ColumnWidth = 18
        Columns("D:D").ColumnWidth = 18
        Columns("E:E").ColumnWidth = 10
        Columns("F:F").ColumnWidth = 10
        
        Range("F2").Select
        'end fmt
        
        
        count = count + 1
        Print #1, "-- start " & count
        Print #1, "DROP TABLE IF EXISTS " & TableName & " ; "
    
        Print #1, "CREATE TABLE " & TableName & " ( "

        For ListRow2 = 2 To Range("C65536").End(xlUp).Row
        
            ms = Worksheets(sheetName).Range("B" & ListRow2).Value
            zdm = Worksheets(sheetName).Range("C" & ListRow2).Value
            lx = Worksheets(sheetName).Range("D" & ListRow2).Value
            isK = Worksheets(sheetName).Range("E" & ListRow2).Value
            
            If ListRow2 > 2 Then Print #1, ","
            
            If Trim(isK) = "Y" Then Print #1, "  " & zdm & "  " & " " & Trim(lx) & " not null primary key COMMENT '" & ms & "'";
            If Trim(isK) <> "Y" Then Print #1, "  " & zdm & "  " & " " & Trim(lx) & " COMMENT '" & ms & "'";
           
        Next ListRow2
        
        Print #1, ""
    
        '写结尾
        Print #1, " );"
        Print #1, "-- end " & count
        Print #1, ""
        Print #1, ""
        '执行后改变内容
        Sheets("目录").Select
        Worksheets("目录").Range("D" & ListRow).Select
        ActiveCell.FormulaR1C1 = "否"
    End If
  Next ListRow
  Close #1
  
  Sheets("目录").Select
  Dim Result As VbMsgBoxStyle
  Result = MsgBox("生成完毕,请查看:" & FileName, vbOKOnly + vbInformation, "提示")
  
End Sub